package ru.reaperoq.schema

import kotlinx.serialization.Serializable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction
import ru.reaperoq.utils.dbQuery

@Serializable
data class Location(val id: Int, val name: String)

object Locations : Table("locations") {
    val id: Column<Int> = integer("id").autoIncrement()
    val name: Column<String> = varchar("name", 50)

    override val primaryKey = PrimaryKey(id)
}

class LocationsService(private val database: Database) {
    init {
        transaction(database) {
            SchemaUtils.create(Locations)
        }
    }

    suspend fun create(name: String): Int = dbQuery {
        Locations.insert {
            it[Locations.name] = name
        }[Locations.id]
    }

    suspend fun read(id: Int): Location? {
        return dbQuery {
            Locations.select { Locations.id eq id }
                .map { Location(it[Locations.id], it[Locations.name]) }
                .singleOrNull()
        }
    }

    suspend fun readByName(name: String): Location? {
        return dbQuery {
            Locations.select { Locations.name eq name }
                .map { Location(it[Locations.id], it[Locations.name]) }
                .singleOrNull()
        }
    }

    suspend fun update(id: Int, name: String) {
        dbQuery {
            Locations.update({ Locations.id eq id }) {
                it[Locations.name] = name
            }
        }
    }

    suspend fun delete(id: Int) {
        dbQuery {
            Locations.deleteWhere { Locations.id eq id }
        }
    }

    suspend fun readAll(): List<Location> = dbQuery {
        Locations.selectAll()
            .map { Location(it[Locations.id], it[Locations.name]) }
    }
}